﻿using System.Text;
using System.Collections.ObjectModel;
using PM.Entity;
using PM.Common;
using System.Data.OracleClient;
using System.Data;
using System;

namespace PM.Services
{
    public class UserAccountServices
    {
        private static UserAccount GetUserAccountFromDataReader(IDataReader dataReader)
        {
            UserAccount retVal = null;
            if (dataReader != null && !dataReader.IsClosed)
            {
                retVal = new UserAccount();
                retVal.UserName = dataReader["USERNAME"].ToString();
                retVal.Password = dataReader["PASSWORD"].ToString();
                retVal.ClientId = dataReader["CLIENT_ID"].ToString();
                int userTypeId = 0;
                if (int.TryParse(dataReader["USER_TYPE_ID"].ToString(), out userTypeId))
                {
                    retVal.UserTypeId = userTypeId;
                }
                int active = 0;
                int.TryParse(dataReader["ACTIVE"].ToString(), out active);
                if (active == 1)
                {
                    retVal.Active = true;
                }
                else
                {
                    retVal.Active = false;
                }
                
            }
            return retVal;
        }

        public static bool CheckExist(string userName)
        {
            bool retVal = false;
            OracleConnection con = new OracleConnection();

            try
            {

                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select 1 from user_account where username = '" + userName + "'";
                cmd.Connection = con;

                object obj = cmd.ExecuteScalar();
                if (obj != null && obj != DBNull.Value)
                {
                    if (Convert.ToInt32(obj) == 1)
                    {
                        retVal = true;
                    } 
                }
               
            }
            catch (System.Exception exc)
            {
                string s = exc.Message;
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static UserAccount GetUserAccountByUserName(string userName)
        {
            UserAccount retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from user_account where username = '" + userName + "' and active = 1";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader.Read())
                    {
                        retVal = GetUserAccountFromDataReader(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                string s = exc.Message;
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static UserAccountCollection GetUserAccountCollection()
        {
            UserAccountCollection retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from user_account";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    retVal = new UserAccountCollection();
                    while (dataReader.Read())
                    {
                        UserAccount item = GetUserAccountFromDataReader(dataReader);
                        if (item != null)
                            retVal.Add(item);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }

        public static DataTable GetUserAccountCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "select * from user_account";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        public static DataTable GetCustomUserAccountCollectionDataTable()
        {
            DataTable retVal = null;
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "SELECT USERNAME, CASE WHEN USER_TYPE_ID = '1' THEN  'Administrator' ELSE 'Normal User' END AS USER_TYPE FROM USER_ACCOUNT";
                cmd.Connection = con;

                using (IDataReader dataReader = cmd.ExecuteReader())
                {
                    if (dataReader != null)
                    {
                        retVal = new DataTable();
                        retVal.Load(dataReader);
                    }
                }
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
            return retVal;
        }


        public static void Add(UserAccount userAccount)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "insert into user_account(USERNAME,PASSWORD,USER_TYPE_ID, CLIENT_ID, ACTIVE)" +
                                  "values(:v1,:v2,:v3, :v4, :v5)";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = userAccount.UserName;
                cmd.Parameters.Add(":v2", OracleType.VarChar).Value = userAccount.Password;
                cmd.Parameters.Add(":v3", OracleType.Number).Value = userAccount.UserTypeId;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = userAccount.ClientId;
                if (userAccount.Active)
                {
                    cmd.Parameters.Add(":v5", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v5", OracleType.Number).Value = 0;
                }
               

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Delete(string userName)
        {
            OracleConnection con = new OracleConnection();
            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "delete from user_account where username = '" + userName + "' and active = 0";
                cmd.Connection = con;

                cmd.ExecuteNonQuery();
            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void Update(UserAccount userAccount)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
                con.Open();
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update user_account set " +                                  
                                  "USER_TYPE_ID = :v3 " +
                                  "CLIENT_ID = :v4 " +
                                  "ACTIVE = :v5 " +
                                  "where USERNAME = :v1";
                cmd.Parameters.Add(":v1", OracleType.VarChar).Value = userAccount.UserName;                
                cmd.Parameters.Add(":v3", OracleType.Number).Value = userAccount.UserTypeId;
                cmd.Parameters.Add(":v4", OracleType.VarChar).Value = userAccount.ClientId;
                if (userAccount.Active)
                {
                    cmd.Parameters.Add(":v5", OracleType.Number).Value = 1;
                }
                else
                {
                    cmd.Parameters.Add(":v5", OracleType.Number).Value = 0;
                }

                cmd.Connection = con;
                cmd.ExecuteNonQuery();

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

        public static void UpdateCollection(UserAccountCollection userAccountCollection)
        {
            OracleConnection con = new OracleConnection();

            try
            {
                con.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;             
                OracleCommand cmd = new OracleCommand();
                cmd.CommandText = "update user_account set " +                                  
                                  "ACTIVE = :v5 " +
                                  "where USERNAME = :v1";
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }
                foreach (UserAccount userAccount in userAccountCollection)
                {
                    cmd.Parameters.Add(":v1", OracleType.VarChar).Value = userAccount.UserName; 
                    if (userAccount.Active)
                    {
                        cmd.Parameters.Add(":v5", OracleType.Number).Value = 1;
                    }
                    else
                    {
                        cmd.Parameters.Add(":v5", OracleType.Number).Value = 0;
                    }

                    cmd.Connection = con;
                    cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();                    
                }               

            }
            catch (System.Exception exc)
            {
                throw;
            }
            finally
            {
                con.Close();
            }
        }

    }
}
